# 访问sqlite数据库
import sqlite3
from typing import List, Optional
import logging
from config import app_config


class ChatMessageFlowRepository:
    """
    聊天消息流程表(chat_messages_flow)的数据访问层
    表结构:
    - id: integer primary key autoincrement
    - message_id: integer not null (关联的消息ID)
    - message_type: varchar(50) not null (消息类型)
    - content: text (消息内容)
    - created_at: DATETIME DEFAULT (datetime('now', 'localtime'))
    - updated_at: DATETIME DEFAULT (datetime('now', 'localtime'))
    """

    def __init__(self):
        """初始化数据库连接"""
        self.db_path = app_config.sqlite_db_path

    def _get_connection(self):
        """获取数据库连接"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # 使结果可以通过列名访问
        return conn

    def create_message_flow(self, message_id: int, message_type: str, content: Optional[str] = None) -> int:
        """
        创建新的聊天消息流程记录
        
        Args:
            message_id: 关联的消息ID
            message_type: 消息类型
            content: 消息内容
            
        Returns:
            int: 新创建记录的ID
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO chat_messages_flow (message_id, message_type, content)
                VALUES (?, ?, ?)
            """, (message_id, message_type, content))
            conn.commit()
            return cursor.lastrowid or 0
        finally:
            conn.close()

    def log_input_audio(self, message_id: int, audio_path: str) -> int:
        """
        创建新的聊天消息流程记录（音频）

        Args:
            message_id: 关联的消息ID
            audio_path: 音频文件路径

        Returns:
            int: 新创建记录的ID
        """
        return self.create_message_flow(message_id, 'input_audio_path', audio_path)
    
    def log_input_text(self, message_id: int, text: str) -> int:
        """
        创建新的聊天消息流程记录（文本）

        Args:
            message_id: 关联的消息ID
            text: 文本内容

        Returns:
            int: 新创建记录的ID
        """
        return self.create_message_flow(message_id, 'input_text', text);

    def log_response_text(self, message_id: int, text: str) -> int:
        """
        创建新的聊天消息流程记录（响应文本）

        Args:
            message_id: 关联的消息ID
            text: 响应文本内容

        Returns:
            int: 新创建记录的ID
        """
        return self.create_message_flow(message_id, 'response_text', text);

    def log_response_audio(self, message_id: int, audio_path: str) -> int:
        """
        创建新的聊天消息流程记录（响应音频）

        Args:
            message_id: 关联的消息ID
            audio_path: 响应音频文件路径

        Returns:
            int: 新创建记录的ID
        """
        return self.create_message_flow(message_id, 'response_audio_path', audio_path);

    def get_message_flows_text_by_message_id(self, message_id: int, message_limit: int = None) -> List[dict]: # type: ignore
        """
        获取指定消息ID的所有聊天消息文本流程记录，按创建时间降序排列，限制返回数量（不传则返回全部）
        
        Args:
            message_id: 消息ID
            message_limit: 返回的最大条数，如果为 None 则查询全部
            
        Returns:
            List[dict]: 流程记录列表
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()

            # 基础 SQL
            sql = """
                SELECT id, message_id, message_type, content, created_at, updated_at
                FROM chat_messages_flow
                WHERE message_id = ? AND message_type IN ('input_text', 'response_text')
                ORDER BY created_at DESC
            """
            params = [message_id]

            # 如果传入了 message_limit，则追加 LIMIT
            if message_limit is not None:
                sql += " LIMIT ?"
                params.append(message_limit)

            cursor.execute(sql, params)
            rows = cursor.fetchall()

            return [dict(row) for row in rows]

        except sqlite3.Error as e:
            logging.error(f"获取聊天消息流程时出错: {e}")
            return []
        finally:
            conn.close()

    def get_message_flow_by_id(self, flow_id: int) -> Optional[dict]:
        """
        根据ID获取聊天消息流程记录
        
        Args:
            flow_id: 流程ID
            
        Returns:
            dict: 流程记录或None（如果未找到）
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, message_id, message_type, content, created_at, updated_at
                FROM chat_messages_flow
                WHERE id = ?
            """, (flow_id,))
            row = cursor.fetchone()
            return dict(row) if row else None
        except sqlite3.Error as e:
            logging.error(f"根据ID获取聊天消息流程时出错: {e}")
            return None
        finally:
            conn.close()

    def get_message_flows_by_message_id(self, message_id: int) -> List[dict]:
        """
        根据消息ID获取所有聊天消息流程记录
        
        Args:
            message_id: 消息ID
            
        Returns:
            List[dict]: 流程记录列表
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, message_id, message_type, content, created_at, updated_at
                FROM chat_messages_flow
                WHERE message_id = ?
                ORDER BY created_at ASC
            """, (message_id,))
            rows = cursor.fetchall()
            return [dict(row) for row in rows]
        except sqlite3.Error as e:
            logging.error(f"根据消息ID获取聊天消息流程时出错: {e}")
            return []
        finally:
            conn.close()

    def get_all_message_flows(self) -> List[dict]:
        """
        获取所有聊天消息流程记录
        
        Returns:
            List[dict]: 所有流程记录列表
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, message_id, message_type, content, created_at, updated_at
                FROM chat_messages_flow
                ORDER BY created_at ASC
            """)
            rows = cursor.fetchall()
            return [dict(row) for row in rows]
        except sqlite3.Error as e:
            logging.error(f"获取所有聊天消息流程时出错: {e}")
            return []
        finally:
            conn.close()

    def update_message_flow_content(self, flow_id: int, content: str) -> bool:
        """
        更新聊天消息流程内容
        
        Args:
            flow_id: 流程ID
            content: 新的消息内容
            
        Returns:
            bool: 更新是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE chat_messages_flow
                SET content = ?, updated_at = datetime('now', 'localtime')
                WHERE id = ?
            """, (content, flow_id))
            conn.commit()
            return cursor.rowcount > 0
        except sqlite3.Error as e:
            logging.error(f"更新聊天消息流程内容时出错: {e}")
            return False
        finally:
            conn.close()

    def update_message_flow_type(self, flow_id: int, message_type: str) -> bool:
        """
        更新聊天消息流程类型
        
        Args:
            flow_id: 流程ID
            message_type: 新的消息类型
            
        Returns:
            bool: 更新是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE chat_messages_flow
                SET message_type = ?, updated_at = datetime('now', 'localtime')
                WHERE id = ?
            """, (message_type, flow_id))
            conn.commit()
            return cursor.rowcount > 0
        except sqlite3.Error as e:
            logging.error(f"更新聊天消息流程类型时出错: {e}")
            return False
        finally:
            conn.close()

    def delete_message_flow(self, flow_id: int) -> bool:
        """
        删除聊天消息流程记录
        
        Args:
            flow_id: 流程ID
            
        Returns:
            bool: 删除是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM chat_messages_flow WHERE id = ?", (flow_id,))
            conn.commit()
            return cursor.rowcount > 0
        except sqlite3.Error as e:
            logging.error(f"删除聊天消息流程时出错: {e}")
            return False
        finally:
            conn.close()

    def delete_message_flows_by_message_id(self, message_id: int) -> int:
        """
        根据消息ID删除所有聊天消息流程记录
        
        Args:
            message_id: 消息ID
            
        Returns:
            int: 删除的记录数量
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM chat_messages_flow WHERE message_id = ?", (message_id,))
            conn.commit()
            return cursor.rowcount
        except sqlite3.Error as e:
            logging.error(f"根据消息ID删除聊天消息流程时出错: {e}")
            return 0
        finally:
            conn.close()


# 创建全局实例
chat_message_flow_repo = ChatMessageFlowRepository()